/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package logic;

import entityBeans.Associatedsequence;
import entityBeans.Dynamicproperty;
import entityBeans.Georeferencedby;
import entityBeans.Georeferencesource;
import entityBeans.Identifiedby;
import entityBeans.Individual;
import entityBeans.Preparation;
import entityBeans.Recordedby;
import entityBeans.Specimen;
import entityBeans.Typestatus;
import java.text.SimpleDateFormat;
import java.util.List;
import xlsBeans.Filter;

/**
 *
 * @author joao
 */
public class SQLGenerate {

    public String specimenSearchSQL (){
        String sqlSpecimen = new String();

        sqlSpecimen = "select *, "
                + "nnDynamic(r.idrecordlevelelement) as dynamicproperty, "
                + "nnRecordedBy(occurrenceelement.idoccurrenceelement) as recordedby, "
                + "nnIndividual(occurrenceelement.idoccurrenceelement) as individual, "
                + "nnPreparation(occurrenceelement.idoccurrenceelement) as preparation, "
                + "nnAssociatedSequence(occurrenceelement.idoccurrenceelement) as associatedsequence, "
                + "nnIdentifiedby(i.ididentificationelement) as identifiedby, "
                + "nnTypestatus(i.ididentificationelement) as typestatus, "
                + "nnGeoreferencedby(l.idlocalityelement) as georeferencedby, "
                + "nnGeoreferencesource(l.idlocalityelement) as georeferencesource "
                + "from specimen s "

                + "left join recordlevelelement r ON s.idrecordlevelelement = r.idrecordlevelelement "
                + "left join taxonomicelement t ON s.idtaxonomicelement = t.idtaxonomicelement "
                + "left join occurrenceelement ON s.idoccurrenceelement = occurrenceelement.idoccurrenceelement "
                + "left join identificationelement i ON s.ididentificationelement = i.ididentificationelement "
                + "left join eventelement e ON s.ideventelement = e.ideventelement "
                + "left join geospatialelement g ON s.idgeospatialelement = g.idgeospatialelement "
                + "left join localityelement l ON s.idlocalityelement = l.idlocalityelement "

                + " left join basisofrecord ON r.idbasisofrecord = basisofrecord.idbasisofrecord"
                + " left join institutioncode ON r.idinstitutioncode = institutioncode.idinstitutioncode"
                + " left join collectioncode ON r.idcollectioncode = collectioncode.idcollectioncode"
                + " left join dataset ON r.iddataset = dataset.iddataset"
                + " left join ownerinstitution ON r.idownerinstitution = ownerinstitution.idownerinstitution"
                + " left join type ON r.idtype = type.idtype"
                + " left join behavior ON occurrenceelement.idbehavior = behavior.idbehavior"
                + " left join disposition ON occurrenceelement.idDisposition = disposition.iddisposition"
                + " left join establishmentmean ON occurrenceelement.idEstablishmentmean = establishmentmean.idestablishmentmean"
                + " left join lifestage ON occurrenceelement.idLifestage = lifestage.idLifestage"
                + " left join reproductivecondition ON occurrenceelement.idReproductivecondition = reproductivecondition.idReproductivecondition"
                + " left join sex ON occurrenceelement.idSex = sex.idsex"
                + " left join Acceptednameusage ON t.idAcceptednameusage = Acceptednameusage.idAcceptednameusage"
                + " left join \"class\" ON t.idClass = \"class\".idClass"
                + " left join Family ON t.idFamily = Family.idFamily"
                + " left join genus  ON t.idGenus = genus.idGenus"
                + " left join Infraspecificepithet ON t.idInfraspecificepithet = Infraspecificepithet.idInfraspecificepithet"
                + " left join Kingdom ON t.idKingdom = Kingdom.idKingdom"
                + " left join Nameaccordingto ON t.idNameaccordingto = Nameaccordingto.idNameaccordingto"
                + " left join Namepublishedin ON t.idNamepublishedin = Namepublishedin.idNamepublishedin"
                + " left join Nomenclaturalcode ON t.idNomenclaturalcode = Nomenclaturalcode.idNomenclaturalcode"
                + " left join \"order\" ON t.idOrder = \"order\".idOrder"
                + " left join Originalnameusage ON t.idOriginalnameusage = Originalnameusage.idOriginalnameusage"
                + " left join Parentnameusage ON t.idParentnameusage = Parentnameusage.idParentnameusage"
                + " left join Phylum ON t.idPhylum = Phylum.idPhylum"
                + " left join Scientificname ON t.idScientificname = Scientificname.idScientificname"
                + " left join Scientificnameauthorship ON t.idScientificnameauthorship = Scientificnameauthorship.idScientificnameauthorship"
                + " left join Specificepithet ON t.idSpecificepithet = Specificepithet.idSpecificepithet"
                + " left join Subgenus ON t.idSubgenus = Subgenus.idSubgenus"
                + " left join Taxonconcept ON t.idTaxonconcept = Taxonconcept.idTaxonconcept"
                + " left join Taxonomicstatus ON t.idTaxonomicstatus = Taxonomicstatus.idTaxonomicstatus"
                + " left join Taxonrank ON t.idTaxonrank = Taxonrank.idTaxonrank"
                + " left join Identificationqualifier ON i.idIdentificationqualifier = Identificationqualifier.idIdentificationqualifier"
                + " left join Habitat ON e.idHabitat = Habitat.idHabitat"
                + " left join Samplingprotocol ON e.idSamplingprotocol = Samplingprotocol.idSamplingprotocol"
                + " left join Continent ON l.idContinent = Continent.idContinent"
                + " left join Country ON l.idCountry = Country.idCountry"
                + " left join Stateprovince ON l.idStateprovince = Stateprovince.idStateprovince"
                + " left join County ON l.idCounty = County.idCounty"
                + " left join Municipality ON l.idMunicipality = Municipality.idMunicipality"
                + " left join Locality ON l.idLocality = Locality.idLocality"
                + " left join Waterbody ON l.idWaterbody = Waterbody.idWaterbody"
                + " left join Islandgroup ON l.idIslandgroup = Islandgroup.idIslandgroup"
                + " left join Island ON l.idIsland = Island.idIsland"
                + " left join georeferenceverificationstatus ON g.idgeoreferenceverificationstatus = georeferenceverificationstatus.idgeoreferenceverificationstatus";

       
        return sqlSpecimen;
    }

    public String whereSQL(List<Filter> f){

         String where = "";
        if (!f.isEmpty()) {
            int index = 0;
            where = where + (" where ");
            for (Filter i : f) {
                if (i.getController().equals("catalognumber")) {
                    where = where + "occurrenceelement.catalognumber=\"" + i.getId() + ((f.size() > index + 1 && i.getController().equals(f.get(index + 1).getController())) ? "\" OR " : "\" and ");
                } else {
                    where = where + i.getController() + ".id" + i.getController() + "=" + i.getId() + ((f.size() > index + 1 && i.getController().equals(f.get(index + 1).getController())) ? " OR " : " and ");
                }
                index++;
            }
            where = where.replaceAll(" and $", "");
            where = where.replaceAll(" OR $", "");
        }

         return where;
    }

    public StringBuffer createSQL(Specimen sp, StringBuffer sql) {

        SimpleDateFormat formatTime = new SimpleDateFormat();
        SimpleDateFormat formatDate = new SimpleDateFormat();

        formatTime.applyPattern("hh:mm:ss");
        formatDate.applyPattern("yyyy/MM/dd");
        //falta CREATE  dos NxN
        sql.append("BEGIN; INSERT INTO recordlevelelement "
                + "(idrecordlevelelement, modified, globaluniqueidentifier, accessrights, idbasisofrecord, idcollectioncode, datageneralization, iddataset, Informationwithheld,"
                + "idinstitutioncode, idOwnerinstitution, rights, rightsholder, idtype, isrestricted) VALUES (DEFAULT, '").append(formatDate.format(sp.getRecordlevelelement().getModified())).append("'").append(", '").append(sp.getRecordlevelelement().getGlobaluniqueidentifier()).append("'").append(", '").append(sp.getRecordlevelelement().getAccessrights()).append("'").append(", ").append(sp.getRecordlevelelement().getBasisofrecord().getIdbasisofrecord()).append(", ").append(sp.getRecordlevelelement().getCollectioncode().getIdcollectioncode()).append(", '").append(sp.getRecordlevelelement().getDatageneralization()).append("'").append(", ").append(sp.getRecordlevelelement().getDataset().getIddataset()).append(", '").append(sp.getRecordlevelelement().getInformationwithheld()).append("'").append(", ").append(sp.getRecordlevelelement().getInstitutioncode().getIdinstitutioncode()).append(", ").append(sp.getRecordlevelelement().getOwnerinstitution().getIdownerinstitution()).append(", '").append(sp.getRecordlevelelement().getRights()).append("'").append(", '").append(sp.getRecordlevelelement().getRightsholder()).append("'").append(", ").append(sp.getRecordlevelelement().getType().getIdtype())
                .append(", ").append(sp.getRecordlevelelement().getIsrestricted()).append("); \n")
                .append("INSERT INTO localityelement "
                + "(idlocalityelement, idcountry, idcontinent, Coordinateprecision, idcounty, Footprintsrs, idisland, "
                + "idislandgroup, idlocality, Locationaccordingto, Locationremark, Maximumdepthinmeters, "
                + "Minimumdepthinmeters, Maximumdistanceabovesurfaceinmeters, Minimumdistanceabovesurfaceinmeters,"
                + "Maximumelevationinmeters, Minimumelevationinmeters, idMunicipality, idStateprovince, "
                + "Verbatimdepth, Verbatimelevation, Verbatimlocality, Verbatimsrs, idWaterbody, highergeograph) VALUES (DEFAULT, ").append(sp.getLocalityelement().getCountry().getIdcountry()).append(", ").append(sp.getLocalityelement().getContinent().getIdcontinent()).append(", '").append(sp.getLocalityelement().getCoordinateprecision()).append("'").append(", ").append(sp.getLocalityelement().getCounty().getIdcounty()).append(", '").append(sp.getLocalityelement().getFootprintsrs()).append("'").append(", ").append(sp.getLocalityelement().getIsland().getIdisland()).append(", ").append(sp.getLocalityelement().getIslandgroup().getIdislandgroup()).append(", ").append(sp.getLocalityelement().getLocality().getIdlocality()).append(", '").append(sp.getLocalityelement().getLocationaccordingto()).append("'").append(", '").append(sp.getLocalityelement().getLocationremark()).append("'").append(", '").append(sp.getLocalityelement().getMaximumdepthinmeters()).append("'").append(", '").append(sp.getLocalityelement().getMinimumdepthinmeters()).append("'").append(", ").append(sp.getLocalityelement().getMaximumdistanceabovesurfaceinmeters() == null ? "DEFAULT "
                : sp.getLocalityelement().getMaximumdistanceabovesurfaceinmeters()).append(", ").append(sp.getLocalityelement().getMinimumdistanceabovesurfaceinmeters() == null ? "DEFAULT "
                : sp.getLocalityelement().getMinimumdistanceabovesurfaceinmeters()).append(", '").append(sp.getLocalityelement().getMaximumelevationinmeters()).append("'").append(", '").append(sp.getLocalityelement().getMinimumelevationinmeters()).append("'").append(", ").append(sp.getLocalityelement().getMunicipality().getIdmunicipality()).append(", ").append(sp.getLocalityelement().getStateprovince().getIdstateprovince()).append(", '").append(sp.getLocalityelement().getVerbatimdepth()).append("'").append(", '").append(sp.getLocalityelement().getVerbatimelevation()).append("'").append(", '").append(sp.getLocalityelement().getVerbatimlocality()).append("'").append(", '").append(sp.getLocalityelement().getVerbatimsrs()).append("'").append(", ").append(sp.getLocalityelement().getWaterbody().getIdwaterbody()).append(", '").append(sp.getLocalityelement().getContinent().getContinent()).append(";").append(sp.getLocalityelement().getWaterbody().getWaterbody()).append(";").append(sp.getLocalityelement().getIslandgroup().getIslandgroup()).append(";").append(sp.getLocalityelement().getIsland().getIsland()).append(";").append(sp.getLocalityelement().getCountry().getCountry()).append(";").append(sp.getLocalityelement().getStateprovince().getStateprovince()).append(";").append(sp.getLocalityelement().getCounty().getCounty()).append(";").append(sp.getLocalityelement().getMunicipality().getMunicipality()).append("'").append("); \n")
                .append("INSERT INTO occurrenceelement "
                + "(idoccurrenceelement, idBehavior, catalognumber, iddisposition, idEstablishmentmean, Individualcount, "
                + "idLifestage, Occurrencedetail, Occurrenceremark, Occurrencestatus, Othercatalognumber, Recordnumber, "
                + "idReproductivecondition, idsex) VALUES (DEFAULT,  ").append(sp.getOccurrenceelement().getBehavior().getIdbehavior()).append(", '").append(sp.getOccurrenceelement().getCatalognumber()).append("'").append(", ").append(sp.getOccurrenceelement().getDisposition().getIddisposition()).append(", ").append(sp.getOccurrenceelement().getEstablishmentmean().getIdestablishmentmean()).append(", ").append(sp.getOccurrenceelement().getIndividualcount() == null ? "DEFAULT "
                : sp.getOccurrenceelement().getIndividualcount()).append(", ").append(sp.getOccurrenceelement().getLifestage().getIdlifestage()).append(", '").append(sp.getOccurrenceelement().getOccurrencedetail()).append("'").append(", '").append(sp.getOccurrenceelement().getOccurrenceremark()).append("'").append(", '").append(sp.getOccurrenceelement().getOccurrencestatus()).append("'").append(", '").append(sp.getOccurrenceelement().getOthercatalognumber()).append("'").append(", '").append(sp.getOccurrenceelement().getRecordnumber()).append("'").append(", ").append(sp.getOccurrenceelement().getReproductivecondition().getIdreproductivecondition()).append(", ").append(sp.getOccurrenceelement().getSex().getIdsex()).append("); \n")
                .append("INSERT INTO geospatialelement "
                + "(idgeospatialelement, verbatimlatitude, Coordinateuncertaintyinmeters, Decimallatitude, Decimallongitude, "
                + "Footprintwkt, Footprintspatialfit, Geodeticdatum, Georeferenceprotocol, Georeferenceremark, "
                + "idGeoreferenceverificationstatus, Pointradiusspatialfit, Verbatimcoordinate, Verbatimcoordinatesystem, "
                + "Verbatimlongitude) VALUES (DEFAULT, '").append(sp.getGeospatialelement().getVerbatimlatitude()).append("'").append(", ").append(sp.getGeospatialelement().getCoordinateuncertaintyinmeters() == null ? "DEFAULT "
                : sp.getGeospatialelement().getCoordinateuncertaintyinmeters()).append(", ").append(sp.getGeospatialelement().getDecimallatitude() == null ? "DEFAULT "
                : sp.getGeospatialelement().getDecimallatitude()).append(", ").append(sp.getGeospatialelement().getDecimallongitude() == null ? "DEFAULT "
                : sp.getGeospatialelement().getDecimallongitude()).append(", '").append(sp.getGeospatialelement().getFootprintwkt()).append("'").append(", '").append(sp.getGeospatialelement().getFootprintspatialfit()).append("'").append(", '").append(sp.getGeospatialelement().getGeodeticdatum()).append("'").append(", '").append(sp.getGeospatialelement().getGeoreferenceprotocol()).append("'").append(", '").append(sp.getGeospatialelement().getGeoreferenceremark()).append("'").append(", ").append(sp.getGeospatialelement().getGeoreferenceverificationstatus().getIdgeoreferenceverificationstatus()).append(", '").append(sp.getGeospatialelement().getPointradiusspatialfit()).append("'").append(", '").append(sp.getGeospatialelement().getVerbatimcoordinate()).append("'").append(", '").append(sp.getGeospatialelement().getVerbatimcoordinatesystem()).append("'").append(", '").append(sp.getGeospatialelement().getVerbatimlongitude()).append("'").append("); \n")
                .append("INSERT INTO taxonomicelement "
                + "(idtaxonomicelement, idkingdom, idAcceptednameusage, idclass, idfamily, idgenus, idInfraspecificepithet, "
                + "idNameaccordingto, Idnamepublishedin, Idnomenclaturalcode, Nomenclaturalstatus, Idorder, "
                + "Idoriginalnameusage, Idparentnameusage, Idphylum, Idscientificname, Idscientificnameauthorship, "
                + "Idspecificepithet, Idsubgenus, Idtaxonconcept, Idtaxonomicstatus, Idtaxonrank, Taxonremark, "
                + "Verbatimtaxonrank, Vernacularname, higherclassification) VALUES (DEFAULT, ").append(sp.getTaxonomicelement().getKingdom().getIdkingdom()).append(", ").append(sp.getTaxonomicelement().getAcceptednameusage().getIdacceptednameusage()).append(", ").append(sp.getTaxonomicelement().getClass1().getIdclass()).append(", ").append(sp.getTaxonomicelement().getFamily().getIdfamily()).append(", ").append(sp.getTaxonomicelement().getGenus().getIdgenus()).append(", ").append(sp.getTaxonomicelement().getInfraspecificepithet().getIdinfraspecificepithet()).append(", ").append(sp.getTaxonomicelement().getNameaccordingto().getIdnameaccordingto()).append(", ").append(sp.getTaxonomicelement().getNamepublishedin().getIdnamepublishedin()).append(", ").append(sp.getTaxonomicelement().getNomenclaturalcode().getIdnomenclaturalcode()).append(", '").append(sp.getTaxonomicelement().getNomenclaturalstatus()).append("'").append(", ").append(sp.getTaxonomicelement().getOrder1().getIdorder()).append(", ").append(sp.getTaxonomicelement().getOriginalnameusage().getIdoriginalnameusage()).append(", ").append(sp.getTaxonomicelement().getParentnameusage().getIdparentnameusage()).append(", ").append(sp.getTaxonomicelement().getPhylum().getIdphylum()).append(", ").append(sp.getTaxonomicelement().getScientificname().getIdscientificname()).append(", ").append(sp.getTaxonomicelement().getScientificnameauthorship().getIdscientificnameauthorship()).append(", ").append(sp.getTaxonomicelement().getSpecificepithet().getIdspecificepithet()).append(", ").append(sp.getTaxonomicelement().getSubgenus().getIdsubgenus()).append(", ").append(sp.getTaxonomicelement().getTaxonconcept().getIdtaxonconcept()).append(", ").append(sp.getTaxonomicelement().getTaxonomicstatus().getIdtaxonomicstatus()).append(", ").append(sp.getTaxonomicelement().getTaxonrank().getIdtaxonrank()).append(", '").append(sp.getTaxonomicelement().getTaxonremark()).append("'").append(", '").append(sp.getTaxonomicelement().getVerbatimtaxonrank()).append("'").append(", '").append(sp.getTaxonomicelement().getVernacularname()).append("'").append(", '").append(sp.getTaxonomicelement().getKingdom().getKingdom()).append(";").append(sp.getTaxonomicelement().getPhylum().getPhylum()).append(";").append(sp.getTaxonomicelement().getClass1().getClass1()).append(";").append(sp.getTaxonomicelement().getOrder1().getOrder()).append(";").append(sp.getTaxonomicelement().getFamily().getFamily()).append(";").append(sp.getTaxonomicelement().getGenus().getGenus()).append(";").append(sp.getTaxonomicelement().getSubgenus().getSubgenus()).append(";").append(sp.getTaxonomicelement().getSpecificepithet().getSpecificepithet()).append(";").append(sp.getTaxonomicelement().getInfraspecificepithet().getInfraspecificepithet()).append(";").append(sp.getTaxonomicelement().getScientificname().getScientificname()).append("'").append("); \n")
                .append("INSERT INTO eventelement "
                + "(ideventelement, idhabitat, Eventdate, Eventremark, Eventtime, Fieldnote, Fieldnumber, Samplingeffort, "
                + "Idsamplingprotocol, Verbatimeventdate) VALUES (DEFAULT, ").append(sp.getEventelement().getHabitat().getIdhabitat()).append(", ").append(sp.getEventelement().getEventdate() == null ? " DEFAULT " : "'" + formatDate.format(sp.getEventelement().getEventdate()) + "'").append(", '").append(sp.getEventelement().getEventremark()).append("'").append(", ").append(sp.getEventelement().getEventtime() == null ? " DEFAULT " : "'" + formatTime.format(sp.getEventelement().getEventtime()) + "'").append(", '").append(sp.getEventelement().getFieldnote()).append("'").append(", '").append(sp.getEventelement().getFieldnumber()).append("'").append(", '").append(sp.getEventelement().getSamplingeffort()).append("'").append(", ").append(sp.getEventelement().getSamplingprotocol().getIdsamplingprotocol()).append(", '").append(sp.getEventelement().getVerbatimeventdate()).append("'").append("); \n")
                .append("INSERT INTO identificationelement "
                + "(ididentificationelement, Dateidentified, Ididentificationqualifier, Identificationremark) VALUES (DEFAULT, ").append(sp.getIdentificationelement().getDateidentified() == null ? " DEFAULT " : "'" + formatDate.format(sp.getIdentificationelement().getDateidentified()) + "'").append(", ").append(sp.getIdentificationelement().getIdentificationqualifier().getIdidentificationqualifier()).append(", '").append(sp.getIdentificationelement().getIdentificationremark()).append("'").append("); \n")
                .append("INSERT INTO specimen (idspecimen, idrecordlevelelement, idoccurrenceelement, ideventelement, "
                + "ididentificationelement, idtaxonomicelement, idgeospatialelement, idlocalityelement) VALUES (DEFAULT, "
                + "(SELECT currval('recordlevelelements_idrecordlevelelements_seq')), (SELECT currval('occurrence_idoccurrence_seq')), "
                + "(SELECT currval('eventelements_ideventelements_seq')), (SELECT currval('identificationelements_ididentificationelements_seq')), "
                + "(SELECT currval('taxonomicelements_idtaxonomicelements_seq')), (SELECT currval('geospatialelements_idgeospatialelements_seq')), "
                + "(SELECT currval('localityelements_idlocalityelements_seq'))); \n");
        //NxN
        for (Dynamicproperty aux : sp.getRecordlevelelement().getDynamicpropertyList()) {
            sql = sql.append("INSERT INTO recordleveldynamicproperty (idrecordlevelelement, Iddynamicproperty) VALUES (")
                    .append("(SELECT currval('recordlevelelements_idrecordlevelelements_seq'))").append(", ")
                    .append(aux.getIddynamicproperty()).append("); ");
        }
        for (Associatedsequence aux : sp.getOccurrenceelement().getAssociatedsequenceList()) {
            sql = sql.append("INSERT INTO occurrenceassociatedsequence (Idoccurrenceelement, Idassociatedsequence) VALUES (")
                    .append("(SELECT currval('occurrence_idoccurrence_seq'))").append(", ").append(aux.getIdassociatedsequence())
                    .append("); ");
        }
        for (Individual aux : sp.getOccurrenceelement().getIndividualList()) {
            sql = sql.append("INSERT INTO occurrenceindividual (Idoccurrenceelement, Idindividual) VALUES (")
                    .append("(SELECT currval('occurrence_idoccurrence_seq'))").append(", ").append(aux.getIdindividual())
                    .append("); ");
        }
        for (Preparation aux : sp.getOccurrenceelement().getPreparationList()) {
            sql = sql.append("INSERT INTO occurrencepreparation (Idoccurrenceelement, Idpreparation) VALUES (")
                    .append("(SELECT currval('occurrence_idoccurrence_seq'))").append(", ").append(aux.getIdpreparation())
                    .append("); ");
        }
        for (Recordedby aux : sp.getOccurrenceelement().getRecordedbyList()) {
            sql = sql.append("INSERT INTO occurrencerecordedby (Idoccurrenceelement, Idrecordedby) VALUES (")
                    .append("(SELECT currval('occurrence_idoccurrence_seq'))").append(", ").append(aux.getIdrecordedby())
                    .append("); ");
        }
        for (Identifiedby aux : sp.getIdentificationelement().getIdentifiedbyList()) {
            sql = sql.append("INSERT INTO identificationidentifiedby (Ididentificationelement, Ididentifiedby) VALUES (")
                    .append("(SELECT currval('identificationelements_ididentificationelements_seq'))").append(", ")
                    .append(aux.getIdidentifiedby()).append("); ");
        }
        for (Typestatus aux : sp.getIdentificationelement().getTypestatusList()) {
            sql = sql.append("INSERT INTO identificationtypestatus (Ididentificationelement, Idtypestatus) VALUES (")
                    .append("(SELECT currval('identificationelements_ididentificationelements_seq'))").append(", ")
                    .append(aux.getIdtypestatus()).append("); ");
        }
        for (Georeferencedby aux : sp.getLocalityelement().getGeoreferencedbyList()) {
            sql = sql.append("INSERT INTO localitygeoreferencedby (Idlocalityelement, Idgeoreferencedby) VALUES (")
                    .append("(SELECT currval('localityelements_idlocalityelements_seq'))").append(", ")
                    .append(aux.getIdgeoreferencedby()).append("); ");
        }
        for (Georeferencesource aux : sp.getLocalityelement().getGeoreferencesourceList()) {
            sql = sql.append("INSERT INTO localitygeoreferencesource (Idlocalityelement, Idgeoreferencesource) VALUES (")
                    .append("(SELECT currval('localityelements_idlocalityelements_seq'))").append(", ")
                    .append(aux.getIdgeoreferencesource()).append("); ");
        }
        //log
        sql = sql.append(" \nINSERT INTO log VALUES (DEFAULT, DEFAULT, DEFAULT, DEFAULT, 'create', 'specimen', 'spreadsheetsync', ")
                .append("(SELECT currval('specimenelement_idspecimenelement_seq'))")
                .append(", DEFAULT); COMMIT");

        sql = sql.append(" \n\n");

        return sql;

    }

    public StringBuffer updateSQL(Specimen sp, StringBuffer sql) {

        SimpleDateFormat formatTime = new SimpleDateFormat();
        SimpleDateFormat formatDate = new SimpleDateFormat();

        formatTime.applyPattern("hh:mm:ss");
        formatDate.applyPattern("yyyy/MM/dd");
        //falta UPDATE  dos NxN //BEGIN COMMIT parece nao ter uso
        sql.append("BEGIN; UPDATE recordlevelelement SET modified='").append(formatDate.format(sp.getRecordlevelelement().getModified())).append("'").append(", accessrights=E'").append(sp.getRecordlevelelement().getAccessrights()).append("'").append(", idbasisofrecord=").append(sp.getRecordlevelelement().getBasisofrecord().getIdbasisofrecord()).append(", idcollectioncode=").append(sp.getRecordlevelelement().getCollectioncode().getIdcollectioncode()).append(", datageneralization=E'").append(sp.getRecordlevelelement().getDatageneralization()).append("'").append(", iddataset=").append(sp.getRecordlevelelement().getDataset().getIddataset()).append(", Informationwithheld=E'").append(sp.getRecordlevelelement().getInformationwithheld()).append("'").append(", idinstitutioncode=").append(sp.getRecordlevelelement().getInstitutioncode().getIdinstitutioncode()).append(", idOwnerinstitution=").append(sp.getRecordlevelelement().getOwnerinstitution().getIdownerinstitution()).append(", rights=E'").append(sp.getRecordlevelelement().getRights()).append("'").append(", rightsholder=E'").append(sp.getRecordlevelelement().getRightsholder()).append("'").append(", idtype=").append(sp.getRecordlevelelement().getType().getIdtype()).append(", isrestricted=").append(sp.getRecordlevelelement().getIsrestricted()).append(" WHERE idrecordlevelelement=").append(sp.getRecordlevelelement().getIdrecordlevelelement()).append("; \n")
                .append("UPDATE localityelement SET idcountry=").append(sp.getLocalityelement().getCountry().getIdcountry()).append(", idcontinent=").append(sp.getLocalityelement().getContinent().getIdcontinent()).append(", Coordinateprecision=E'").append(sp.getLocalityelement().getCoordinateprecision()).append("'").append(", idcounty=").append(sp.getLocalityelement().getCounty().getIdcounty()).append(", Footprintsrs=E'").append(sp.getLocalityelement().getFootprintsrs()).append("'").append(", idisland=").append(sp.getLocalityelement().getIsland().getIdisland()).append(", idislandgroup=").append(sp.getLocalityelement().getIslandgroup().getIdislandgroup()).append(", idlocality=").append(sp.getLocalityelement().getLocality().getIdlocality()).append(", Locationaccordingto=E'").append(sp.getLocalityelement().getLocationaccordingto()).append("'").append(", Locationremark=E'").append(sp.getLocalityelement().getLocationremark()).append("'").append(", Maximumdepthinmeters=E'").append(sp.getLocalityelement().getMaximumdepthinmeters()).append("'").append(", Minimumdepthinmeters=E'").append(sp.getLocalityelement().getMinimumdepthinmeters()).append("'").append(", Maximumdistanceabovesurfaceinmeters=")//float
                .append(sp.getLocalityelement().getMaximumdistanceabovesurfaceinmeters() == null ? "DEFAULT "
                : sp.getLocalityelement().getMaximumdistanceabovesurfaceinmeters()).append(", Minimumdistanceabovesurfaceinmeters=")//float
                .append(sp.getLocalityelement().getMinimumdistanceabovesurfaceinmeters() == null ? "DEFAULT "
                : sp.getLocalityelement().getMinimumdistanceabovesurfaceinmeters()).append(", Maximumelevationinmeters=E'").append(sp.getLocalityelement().getMaximumelevationinmeters()).append("'").append(", Minimumelevationinmeters=E'").append(sp.getLocalityelement().getMinimumelevationinmeters()).append("'").append(", idMunicipality=").append(sp.getLocalityelement().getMunicipality().getIdmunicipality()).append(", idStateprovince=").append(sp.getLocalityelement().getStateprovince().getIdstateprovince()).append(", Verbatimdepth=E'").append(sp.getLocalityelement().getVerbatimdepth()).append("'").append(", Verbatimelevation=E'").append(sp.getLocalityelement().getVerbatimelevation()).append("'").append(", Verbatimlocality=E'").append(sp.getLocalityelement().getVerbatimlocality()).append("'").append(", Verbatimsrs=E'").append(sp.getLocalityelement().getVerbatimsrs()).append("'").append(", idWaterbody=").append(sp.getLocalityelement().getWaterbody().getIdwaterbody()).append(", highergeograph=E'").append(sp.getLocalityelement().getContinent().getContinent()).append(";").append(sp.getLocalityelement().getWaterbody().getWaterbody()).append(";").append(sp.getLocalityelement().getIslandgroup().getIslandgroup()).append(";").append(sp.getLocalityelement().getIsland().getIsland()).append(";").append(sp.getLocalityelement().getCountry().getCountry()).append(";").append(sp.getLocalityelement().getStateprovince().getStateprovince()).append(";").append(sp.getLocalityelement().getCounty().getCounty()).append(";").append(sp.getLocalityelement().getMunicipality().getMunicipality()).append("'").append(" WHERE idlocalityelement=").append(sp.getLocalityelement().getIdlocalityelement()).append("; \n")
                .append("UPDATE occurrenceelement SET ").append(" idBehavior=").append(sp.getOccurrenceelement().getBehavior().getIdbehavior()).append(", catalognumber=E'").append(sp.getOccurrenceelement().getCatalognumber()).append("'").append(", iddisposition=").append(sp.getOccurrenceelement().getDisposition().getIddisposition()).append(", idEstablishmentmean=").append(sp.getOccurrenceelement().getEstablishmentmean().getIdestablishmentmean()).append(", Individualcount=")//integer
                .append(sp.getOccurrenceelement().getIndividualcount() == null ? "DEFAULT "
                : sp.getOccurrenceelement().getIndividualcount()).append(", idLifestage=").append(sp.getOccurrenceelement().getLifestage().getIdlifestage()).append(", Occurrencedetail=E'").append(sp.getOccurrenceelement().getOccurrencedetail()).append("'").append(", Occurrenceremark=E'").append(sp.getOccurrenceelement().getOccurrenceremark()).append("'").append(", Occurrencestatus=E'").append(sp.getOccurrenceelement().getOccurrencestatus()).append("'").append(", Othercatalognumber=E'").append(sp.getOccurrenceelement().getOthercatalognumber()).append("'").append(", Recordnumber=E'").append(sp.getOccurrenceelement().getRecordnumber()).append("'").append(", idReproductivecondition=").append(sp.getOccurrenceelement().getReproductivecondition().getIdreproductivecondition()).append(", idsex=").append(sp.getOccurrenceelement().getSex().getIdsex()).append(" WHERE idoccurrenceelement=").append(sp.getOccurrenceelement().getIdoccurrenceelement()).append("; \n")
                .append("UPDATE geospatialelement SET verbatimlatitude=E'").append(sp.getGeospatialelement().getVerbatimlatitude()).append("'").append(", Coordinateuncertaintyinmeters=").append(sp.getGeospatialelement().getCoordinateuncertaintyinmeters() == null ? "DEFAULT "
                : sp.getGeospatialelement().getCoordinateuncertaintyinmeters()).append(", Decimallatitude=").append(sp.getGeospatialelement().getDecimallatitude() == null ? "DEFAULT "
                : sp.getGeospatialelement().getDecimallatitude()).append(", Decimallongitude=").append(sp.getGeospatialelement().getDecimallongitude() == null ? "DEFAULT "
                : sp.getGeospatialelement().getDecimallongitude()).append(", Footprintwkt=E'").append(sp.getGeospatialelement().getFootprintwkt()).append("'").append(", Footprintspatialfit=E'").append(sp.getGeospatialelement().getFootprintspatialfit()).append("'").append(", Geodeticdatum=E'").append(sp.getGeospatialelement().getGeodeticdatum()).append("'").append(", Georeferenceprotocol=E'").append(sp.getGeospatialelement().getGeoreferenceprotocol()).append("'").append(", Georeferenceremark=E'").append(sp.getGeospatialelement().getGeoreferenceremark()).append("'").append(", idGeoreferenceverificationstatus=").append(sp.getGeospatialelement().getGeoreferenceverificationstatus().getIdgeoreferenceverificationstatus()).append(", Pointradiusspatialfit=E'").append(sp.getGeospatialelement().getPointradiusspatialfit()).append("'").append(", Verbatimcoordinate=E'").append(sp.getGeospatialelement().getVerbatimcoordinate()).append("'").append(", Verbatimcoordinatesystem=E'").append(sp.getGeospatialelement().getVerbatimcoordinatesystem()).append("'").append(", Verbatimlongitude=E'").append(sp.getGeospatialelement().getVerbatimlongitude()).append("'").append(" WHERE idgeospatialelement=").append(sp.getGeospatialelement().getIdgeospatialelement()).append("; \n")
                .append("UPDATE taxonomicelement SET idkingdom=").append(sp.getTaxonomicelement().getKingdom().getIdkingdom()).append(", idAcceptednameusage=").append(sp.getTaxonomicelement().getAcceptednameusage().getIdacceptednameusage()).append(", idclass=").append(sp.getTaxonomicelement().getClass1().getIdclass()).append(", idfamily=").append(sp.getTaxonomicelement().getFamily().getIdfamily()).append(", idgenus=").append(sp.getTaxonomicelement().getGenus().getIdgenus()).append(", idInfraspecificepithet=").append(sp.getTaxonomicelement().getInfraspecificepithet().getIdinfraspecificepithet()).append(", idNameaccordingto=").append(sp.getTaxonomicelement().getNameaccordingto().getIdnameaccordingto()).append(", Idnamepublishedin=").append(sp.getTaxonomicelement().getNamepublishedin().getIdnamepublishedin()).append(", Idnomenclaturalcode=").append(sp.getTaxonomicelement().getNomenclaturalcode().getIdnomenclaturalcode()).append(", Nomenclaturalstatus=E'").append(sp.getTaxonomicelement().getNomenclaturalstatus()).append("'").append(", Idorder=").append(sp.getTaxonomicelement().getOrder1().getIdorder()).append(", Idoriginalnameusage=").append(sp.getTaxonomicelement().getOriginalnameusage().getIdoriginalnameusage()).append(", Idparentnameusage=").append(sp.getTaxonomicelement().getParentnameusage().getIdparentnameusage()).append(", Idphylum=").append(sp.getTaxonomicelement().getPhylum().getIdphylum()).append(", Idscientificname=").append(sp.getTaxonomicelement().getScientificname().getIdscientificname()).append(", Idscientificnameauthorship=").append(sp.getTaxonomicelement().getScientificnameauthorship().getIdscientificnameauthorship()).append(", Idspecificepithet=").append(sp.getTaxonomicelement().getSpecificepithet().getIdspecificepithet()).append(", Idsubgenus=").append(sp.getTaxonomicelement().getSubgenus().getIdsubgenus()).append(", Idtaxonconcept=").append(sp.getTaxonomicelement().getTaxonconcept().getIdtaxonconcept()).append(", Idtaxonomicstatus=").append(sp.getTaxonomicelement().getTaxonomicstatus().getIdtaxonomicstatus()).append(", Idtaxonrank=").append(sp.getTaxonomicelement().getTaxonrank().getIdtaxonrank()).append(", Taxonremark=E'").append(sp.getTaxonomicelement().getTaxonremark()).append("'").append(", Verbatimtaxonrank=E'").append(sp.getTaxonomicelement().getVerbatimtaxonrank()).append("'").append(", Vernacularname=E'").append(sp.getTaxonomicelement().getVernacularname()).append("'").append(", higherclassification=E'").append(sp.getTaxonomicelement().getKingdom().getKingdom()).append(";").append(sp.getTaxonomicelement().getPhylum().getPhylum()).append(";").append(sp.getTaxonomicelement().getClass1().getClass1()).append(";").append(sp.getTaxonomicelement().getOrder1().getOrder()).append(";").append(sp.getTaxonomicelement().getFamily().getFamily()).append(";").append(sp.getTaxonomicelement().getGenus().getGenus()).append(";").append(sp.getTaxonomicelement().getSubgenus().getSubgenus()).append(";").append(sp.getTaxonomicelement().getSpecificepithet().getSpecificepithet()).append(";").append(sp.getTaxonomicelement().getInfraspecificepithet().getInfraspecificepithet()).append(";").append(sp.getTaxonomicelement().getScientificname().getScientificname()).append("'").append(" WHERE idtaxonomicelement=").append(sp.getTaxonomicelement().getIdtaxonomicelement()).append("; \n")
                .append("UPDATE eventelement SET idhabitat=").append(sp.getEventelement().getHabitat().getIdhabitat()).append(", Eventdate=")//date
                .append(sp.getEventelement().getEventdate() == null ? " DEFAULT " : "E'" + formatDate.format(sp.getEventelement().getEventdate()) + "'").append(", Eventremark=E'").append(sp.getEventelement().getEventremark()).append("'").append(", Eventtime=")//date
                .append(sp.getEventelement().getEventtime() == null ? " DEFAULT " : "E'" + formatTime.format(sp.getEventelement().getEventtime()) + "'").append(", Fieldnote=E'").append(sp.getEventelement().getFieldnote()).append("'").append(", Fieldnumber=E'").append(sp.getEventelement().getFieldnumber()).append("'").append(", Samplingeffort=E'").append(sp.getEventelement().getSamplingeffort()).append("'").append(", Idsamplingprotocol=").append(sp.getEventelement().getSamplingprotocol().getIdsamplingprotocol()).append(", Verbatimeventdate=E'").append(sp.getEventelement().getVerbatimeventdate()).append("'").append(" WHERE ideventelement=").append(sp.getEventelement().getIdeventelement()).append("; \n")
                .append("UPDATE identificationelement SET ").append(" Dateidentified=")//date
                .append(sp.getIdentificationelement().getDateidentified() == null ? " DEFAULT " : "E'" + formatDate.format(sp.getIdentificationelement().getDateidentified()) + "'").append(", Ididentificationqualifier=").append(sp.getIdentificationelement().getIdentificationqualifier().getIdidentificationqualifier()).append(", Identificationremark=E'").append(sp.getIdentificationelement().getIdentificationremark()).append("'").append(" WHERE ididentificationelement=").append(sp.getIdentificationelement().getIdidentificationelement()).append("; \n")
                .append("DELETE FROM ONLY recordleveldynamicproperty WHERE idrecordlevelelement=").append(sp.getRecordlevelelement().getIdrecordlevelelement()).append("; ").append("DELETE FROM ONLY occurrenceassociatedsequence WHERE idoccurrenceelement=").append(sp.getOccurrenceelement().getIdoccurrenceelement()).append("; ").append("DELETE FROM ONLY occurrenceindividual WHERE idoccurrenceelement=").append(sp.getOccurrenceelement().getIdoccurrenceelement()).append("; ").append("DELETE FROM ONLY occurrencepreparation WHERE idoccurrenceelement=").append(sp.getOccurrenceelement().getIdoccurrenceelement()).append("; ").append("DELETE FROM ONLY occurrencerecordedby WHERE idoccurrenceelement=").append(sp.getOccurrenceelement().getIdoccurrenceelement()).append("; ").append("DELETE FROM ONLY identificationidentifiedby WHERE ididentificationelement=").append(sp.getIdentificationelement().getIdidentificationelement()).append("; ").append("DELETE FROM ONLY identificationtypestatus WHERE ididentificationelement=").append(sp.getIdentificationelement().getIdidentificationelement()).append("; ").append("DELETE FROM ONLY localitygeoreferencedby WHERE idlocalityelement=").append(sp.getLocalityelement().getIdlocalityelement()).append("; ").append("DELETE FROM ONLY localitygeoreferencesource WHERE idlocalityelement=").append(sp.getLocalityelement().getIdlocalityelement()).append("; \n");
        //NxN
        for (Dynamicproperty aux : sp.getRecordlevelelement().getDynamicpropertyList()) {
            sql = sql.append("INSERT INTO recordleveldynamicproperty (idrecordlevelelement, Iddynamicproperty) VALUES (")
                    .append(sp.getRecordlevelelement().getIdrecordlevelelement()).append(", ")
                    .append(aux.getIddynamicproperty()).append("); ");
        }
        for (Associatedsequence aux : sp.getOccurrenceelement().getAssociatedsequenceList()) {
            sql = sql.append("INSERT INTO occurrenceassociatedsequence (Idoccurrenceelement, Idassociatedsequence) VALUES (")
                    .append(sp.getOccurrenceelement().getIdoccurrenceelement()).append(", ")
                    .append(aux.getIdassociatedsequence()).append("); ");
        }
        for (Individual aux : sp.getOccurrenceelement().getIndividualList()) {
            sql = sql.append("INSERT INTO occurrenceindividual (Idoccurrenceelement, Idindividual) VALUES (")
                    .append(sp.getOccurrenceelement().getIdoccurrenceelement()).append(", ")
                    .append(aux.getIdindividual()).append("); ");
        }
        for (Preparation aux : sp.getOccurrenceelement().getPreparationList()) {
            sql = sql.append("INSERT INTO occurrencepreparation (Idoccurrenceelement, Idpreparation) VALUES (")
                    .append(sp.getOccurrenceelement().getIdoccurrenceelement()).append(", ")
                    .append(aux.getIdpreparation()).append("); ");
        }
        for (Recordedby aux : sp.getOccurrenceelement().getRecordedbyList()) {
            sql = sql.append("INSERT INTO occurrencerecordedby (Idoccurrenceelement, Idrecordedby) VALUES (")
                    .append(sp.getOccurrenceelement().getIdoccurrenceelement()).append(", ")
                    .append(aux.getIdrecordedby()).append("); ");
        }
        for (Identifiedby aux : sp.getIdentificationelement().getIdentifiedbyList()) {
            sql = sql.append("INSERT INTO identificationidentifiedby (Ididentificationelement, Ididentifiedby) VALUES (")
                    .append(sp.getIdentificationelement().getIdidentificationelement()).append(", ")
                    .append(aux.getIdidentifiedby()).append("); ");
        }
        for (Typestatus aux : sp.getIdentificationelement().getTypestatusList()) {
            sql = sql.append("INSERT INTO identificationtypestatus (Ididentificationelement, Idtypestatus) VALUES (")
                    .append(sp.getIdentificationelement().getIdidentificationelement()).append(", ")
                    .append(aux.getIdtypestatus()).append("); ");
        }
        for (Georeferencedby aux : sp.getLocalityelement().getGeoreferencedbyList()) {
            sql = sql.append("INSERT INTO localitygeoreferencedby (Idlocalityelement, Idgeoreferencedby) VALUES (")
                    .append(sp.getLocalityelement().getIdlocalityelement()).append(", ")
                    .append(aux.getIdgeoreferencedby()).append("); ");
        }
        for (Georeferencesource aux : sp.getLocalityelement().getGeoreferencesourceList()) {
            sql = sql.append("INSERT INTO localitygeoreferencesource (Idlocalityelement, Idgeoreferencesource) VALUES (")
                    .append(sp.getLocalityelement().getIdlocalityelement()).append(", ")
                    .append(aux.getIdgeoreferencesource()).append("); ");
        }
        //log
        sql = sql.append(" \nINSERT INTO log VALUES (DEFAULT, DEFAULT, DEFAULT, DEFAULT, 'update', 'specimen', 'spreadsheetsync', ")
                .append(sp.getIdspecimen())
                .append(", DEFAULT); COMMIT;");



        sql = sql.append(" \n\n");


//                                .append("UPDATE localityelement SET idcountry=91 WHERE idlocalityelement=19524; ")
//                                .append("UPDATE occurrenceelement SET iddisposition=49 WHERE idoccurrenceelement=47543; ")
//                                .append("UPDATE geospatialelement SET verbatimlatitude=E'esquerda' WHERE idgeospatialelement=38873; ")
//                                .append("UPDATE taxonomicelement SET idkingdom=14 WHERE idtaxonomicelement=48302; ")
//                                .append("UPDATE eventelement SET idhabitat=44 WHERE ideventelement=46909; ")
//                                .append("UPDATE identificationelement SET ididentificationqualifier=24 WHERE ididentificationelement=37672; ");

        return sql;

    }

    public StringBuffer deleteSQL(Specimen sp, StringBuffer sql){

        sql.append("BEGIN; DELETE FROM specimen WHERE idspecimen=").append(sp.getIdspecimen()).append("; ")
                .append("DELETE FROM recordlevelelement WHERE idrecordlevelelement=").append(sp.getRecordlevelelement().getIdrecordlevelelement()).append("; ")
                .append("DELETE FROM localityelement WHERE idlocalityelement=").append(sp.getLocalityelement().getIdlocalityelement()).append("; ")
                .append("DELETE FROM identificationelement WHERE ididentificationelement=").append(sp.getIdentificationelement().getIdidentificationelement()).append("; ")
                .append("DELETE FROM geospatialelement WHERE idgeospatialelement=").append(sp.getGeospatialelement().getIdgeospatialelement()).append("; ")
                .append("DELETE FROM occurrenceelement WHERE idoccurrenceelement=").append(sp.getOccurrenceelement().getIdoccurrenceelement()).append("; ")
                .append("DELETE FROM taxonomicelement WHERE idtaxonomicelement=").append(sp.getTaxonomicelement().getIdtaxonomicelement()).append("; ")
                .append("DELETE FROM eventelement WHERE ideventelement=").append(sp.getEventelement().getIdeventelement()).append("; ")
                .append("INSERT INTO log VALUES (DEFAULT, DEFAULT, DEFAULT, DEFAULT, 'delete', 'specimen', 'spreadsheetsync', ")
                .append(sp.getIdspecimen()).append(", DEFAULT); COMMIT; \n\n");


        return sql;
    }

}
